package com.xxl.job.executor.service.impl;

import java.sql.Connection;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.springframework.jdbc.datasource.DataSourceUtils;
import org.springframework.stereotype.Service;

import com.xxl.job.core.log.XxlJobLogger;
import com.xxl.job.executor.common.util.DBUtils;
import com.xxl.job.executor.common.util.SpringContextHolder;
import com.xxl.job.executor.service.IMonitorGrantryStatisService;


/**
 * 监视门架统计数据类
 * @author Administrator  
 *
 */
@Service("MonitorGrantryStatisService")
public class MonitorGrantryStatisService implements IMonitorGrantryStatisService{
	 Connection connPre = null;
	
	@Override
	public void transfer() throws Exception {
		
		 XxlJobLogger.log("=====开始执行业务操作===");
	    try {
	    DataSource dataSourcePre = SpringContextHolder.getBean("dataSourcePre");
        connPre = DataSourceUtils.getConnection(dataSourcePre);
        connPre.setAutoCommit(false);

        //统计门架监视明细数据
        monitorStatisGrantry(connPre);       
        
        //拆包待发送表
        analyzePackageNoSend(connPre);
         //从千分接口获取清风数据
        sysnMontiroInfoRecv(connPre) ;
        
        connPre.commit();     
        
        String sqlPro = "truncate table TB_STATIS_MONITOR_ETCTU_TEMP ";
        DBUtils.execute(connPre, sqlPro);
        XxlJobLogger.log("[普通]重置临时表成功");

        connPre.commit();

        XxlJobLogger.log("=====完成执行业务操作===");
	    }catch(Exception e){
            if (connPre != null) {
                try {
                    connPre.rollback();
                    XxlJobLogger.log("回滚成功.");
                }catch (Exception e1){
                    e1.printStackTrace();
                }
            }
            XxlJobLogger.log("抛出异常："+e.getMessage());
            throw e;
        }finally{
            if (connPre != null) {
                try {
                    connPre.setAutoCommit(true);
                    connPre.close();
                }catch (Exception e){
                    e.printStackTrace();
                }
            }
        }
	}
	
	
	/**
	 * 从千分接口获取清风数据
	 * @param conn
	 * @throws Exception
	 */
	private void sysnMontiroInfoRecv(Connection conn)  throws Exception{
		String sql =" merge into TB_STATIS_MONITOR_ETCTU a \r\n" + 
				"	using (\r\n" + 
				"    select replace(t.TRADEDATE,'-','') as TRADEDATE,t.GRANTRYPASSGEN,t.GRANTRYPASSUPLOAD,GRANTRYPASSMERGE from TB_CHARGE_MONITOR_INTF_RECV t \r\n" + 
				"	)  b\r\n" + 
				"	on (a.statis_date=b.tradedate)\r\n" + 
				"	WHEN MATCHED THEN\r\n" + 
				"   update set a.CL_EXIT_COUNT=b.GRANTRYPASSGEN,a.CL_EXIT_SEND=b.GRANTRYPASSUPLOAD\r\n" + 
				"	WHEN NOT MATCHED THEN \r\n" + 
				"   INSERT (a.SEQ_ID,a.STATIS_DATE,a.CL_EXIT_COUNT,a.CL_EXIT_SEND,a.INSERT_TIME,a.UPDATE_TIME)\r\n" + 
				"   values (SEQ_TB_STATIS_MONITOR_EXETCPU.nextval,b.TRADEDATE,b.GRANTRYPASSGEN,b.GRANTRYPASSUPLOAD,sysdate,sysdate) ";
		 Integer count = DBUtils.execute(conn, sql);
	     XxlJobLogger.log("[普通]从千分接口获取清风数据条数(条):" + count);
	}
	
	
	private void analyzePackageNoSend(Connection conn)  throws Exception{
		String sql = "select JSONCONTENT from dealdata_exception where ERRORTYPE=3 and ERRORSENDINTF='BASIC_ETCTU' " +
                "and ERRORTIME>=to_date('2020-05-06 00:00:00','yyyy-mm-dd hh24:mi:ss') ";
		  List<Map<String, Object>> list = DBUtils.queryData(conn, sql);
	        XxlJobLogger.log("[普通]待发送数据容器中总共含(条):" + list.size());

	        Map<String, Integer> cntMap = DBUtils.statisJsonGrantryContentCnt(list);
	        XxlJobLogger.log("[普通]待发送数据已统计完成日期个数为(条):" + cntMap.size());
	        
	        String strDate = "";
	        int loop = 0;
	        Integer nCnt = 0;
	        Iterator<Map.Entry<String, Integer>> it = cntMap.entrySet().iterator();
	        while (it.hasNext()) {
	            //组装新的包名
	            Map.Entry<String, Integer> entry = it.next();
	            strDate = entry.getKey();
	            if (strDate == null || strDate.isEmpty()) {
	                continue;
	            }
	            nCnt = entry.getValue();

	            sql = "insert into TB_STATIS_MONITOR_ETCTU_TEMP(statis_date,pro_nosend) " +
	                  "values('"+strDate+"',"+nCnt+")";
	            DBUtils.execute(conn, sql);

	            ++loop;
	        }
	        XxlJobLogger.log("[普通]待发送数据日期临时数据已插入(条):" + loop);
	        
	        sql = "MERGE INTO TB_STATIS_MONITOR_ETCTU a " +
	                "USING TB_STATIS_MONITOR_ETCTU_TEMP b " +
	                "ON (a.STATIS_DATE=b.STATIS_DATE) " +
	                "WHEN MATCHED THEN " +
	                "  update set a.PRO_NOSEND=b.PRO_NOSEND+nvl(a.PRO_NOSEND,0),UPDATE_TIME=sysdate " +
	                "WHEN NOT MATCHED THEN " +
	                "  INSERT (a.SEQ_ID,a.STATIS_DATE,a.PRO_TOTAL,a.PRO_NOSEND,a.INSERT_TIME,a.UPDATE_TIME) " +
	                "  VALUES (SEQ_TB_STATIS_MONITOR_EXETCPU.nextval,b.STATIS_DATE,b.PRO_NOSEND,b.PRO_NOSEND,sysdate,sysdate) ";
	        Integer count = DBUtils.execute(conn, sql);
	        XxlJobLogger.log("[普通]待发送数据已合并(条):" + count);
	        
	       // sql = "update TB_STATIS_MONITOR_ETCTU a set PRO_NOSEND=nvl(PRO_EXCEPTION,0),PRO_TOTAL=nvl(PRO_SEND,0)+nvl(PRO_EXCEPTION,0) where " +
	        //        "not exists (select 1 from TB_STATIS_MONITOR_EXETCPU_TMP b where a.STATIS_DATE=b.STATIS_DATE) ";
	        //count = DBUtils.execute(conn, sql);
	        //XxlJobLogger.log("[普通]无待发送数据的待发送数更新为0(条):" + count);
	        
	        sql = " truncate table TB_STATIS_MONITOR_ETCTU_TEMP ";
	        count = DBUtils.execute(conn, sql);
	        XxlJobLogger.log("[普通]本次清空待发送临时数据(条):" + count);
	}
	
	private void monitorStatisGrantry(Connection conn) throws Exception { 
		String sql = " merge into  tb_statis_monitor_etctu t  " + 
				"  using  ( " + 
				"  select a.TRADE_DATE,count(1) as PRO_TOTAL,sum(a.recv_state) as PRO_SEND,(count(1)-sum(a.recv_state)) as  PRO_NOSEND," + 
				"  sum(QFSEND_STATE),(count(1)-sum(a.recv_state)) as  PRO_EXCEPTION  " + 
				"  from tb_sync_monitor_etctu_0 a   group by a.trade_date" + 
				"  union " + 
				"  select a.TRADE_DATE,count(1) as PRO_TOTAL,sum(a.recv_state) as PRO_SEND,(count(1)-sum(a.recv_state)) as  PRO_NOSEND," + 
				"  sum(QFSEND_STATE),(count(1)-sum(a.recv_state)) as  PRO_EXCEPTION   " + 
				"  from tb_sync_monitor_etctu_1 a   group by a.trade_date " + 
				"  union " + 
				" select a.TRADE_DATE,count(1) as PRO_TOTAL,sum(a.recv_state) as PRO_SEND,(count(1)-sum(a.recv_state)) as  PRO_NOSEND, " + 
				"  sum(QFSEND_STATE),(count(1)-sum(a.recv_state)) as  PRO_EXCEPTION   " + 
				"  from tb_sync_monitor_etctu_2 a   group by a.trade_date " + 
				"  union " + 
				"  select a.TRADE_DATE,count(1) as PRO_TOTAL,sum(a.recv_state) as PRO_SEND,(count(1)-sum(a.recv_state)) as  PRO_NOSEND, " + 
				"  sum(QFSEND_STATE),(count(1)-sum(a.recv_state)) as  PRO_EXCEPTION   " + 
				"  from tb_sync_monitor_etctu_3 a   group by a.trade_date " + 
				"  union " + 
				"  select a.TRADE_DATE,count(1) as PRO_TOTAL,sum(a.recv_state) as PRO_SEND,(count(1)-sum(a.recv_state)) as  PRO_NOSEND, " + 
				"  sum(QFSEND_STATE),(count(1)-sum(a.recv_state)) as  PRO_EXCEPTION   " + 
				"  from tb_sync_monitor_etctu_4 a   group by a.trade_date " + 
				"  union  " + 
				"  select a.TRADE_DATE,count(1) as PRO_TOTAL,sum(a.recv_state) as PRO_SEND,(count(1)-sum(a.recv_state)) as  PRO_NOSEND, " + 
				"  sum(QFSEND_STATE),(count(1)-sum(a.recv_state)) as  PRO_EXCEPTION   " + 
				"  from tb_sync_monitor_etctu_5 a   group by a.trade_date " + 
				"  union " + 
				"  select a.TRADE_DATE,count(1) as PRO_TOTAL,sum(a.recv_state) as PRO_SEND,(count(1)-sum(a.recv_state)) as  PRO_NOSEND, " + 
				"  sum(QFSEND_STATE),(count(1)-sum(a.recv_state)) as  PRO_EXCEPTION   " + 
				"  from tb_sync_monitor_etctu_6 a   group by a.trade_date " + 
				"  ) b " + 
				"  on ( b.TRADE_DATE=t.statis_date) " + 
				"  when matched then  " + 
				"  update  set t.pro_total=b.PRO_TOTAL,t.pro_send=b.PRO_SEND,t.pro_nosend=b.PRO_NOSEND,t.pro_exception=b.PRO_EXCEPTION,t.update_time=sysdate " + 
				"  when not matched then   " + 
				"  insert (SEQ_ID,STATIS_DATE,PRO_TOTAL,PRO_SEND,PRO_NOSEND,PRO_EXCEPTION,INSERT_TIME,UPDATE_TIME) " + 
				"  values (SEQ_tb_statis_monitor_etctu.Nextval,b.TRADE_DATE,b.PRO_TOTAL,b.PRO_SEND,b.PRO_NOSEND,b.PRO_EXCEPTION,sysdate,sysdate) ";
				Integer count = DBUtils.execute(conn, sql);
				XxlJobLogger.log("[普通]本次统计监视数据(条):" + count);	
	}

}
